-- @owner: @wan005
-- @date: 2022/4/6
-- @testpoint: 创建ustore表,结合分析/分组执行explain plan
-- @modified: by @daiguatutu 2023/9/5 增加清理序列步骤
-- @modified by: xiaqi14 2024/12/23 回显信息变更，优化用例

--step1:建表，插入数据   expect:成功
drop table if exists t_ustore_s_condition_0047_1;
create table t_ustore_s_condition_0047_1(id int,deptno int,name varchar(20),sal int,col_16 int[],b_boolean boolean,f_floor decimal(10,2)) with (storage_type=ustore);

insert into t_ustore_s_condition_0047_1 values(1,1,'1aa',120,'{1,2,3}',true,10.2);
insert into t_ustore_s_condition_0047_1 values(2,1,'2aa',300,'{1,3,3}',false,100.2);
insert into t_ustore_s_condition_0047_1 values(3,1,'3aa',100,'{1,2,3}',true,99.2);
insert into t_ustore_s_condition_0047_1 values(4,1,'4aa',99,'{1,2,3}',true,67.2);
insert into t_ustore_s_condition_0047_1 values(5,1,'5aa',90,'{1,3,3}',false,67.2);

drop table if exists t_ustore_s_condition_0047_2 cascade;
create table t_ustore_s_condition_0047_2(staff_id int not null, highest_degree char(8), graduate_school varchar(64), graduate_date smalldatetime, t_education_note varchar(70)) with (storage_type=ustore);

insert into t_ustore_s_condition_0047_2(staff_id,highest_degree,graduate_school,graduate_date,t_education_note) values(10,'博士','西安电子科技大学','2017-07-06 12:00:00','211');
insert into t_ustore_s_condition_0047_2(staff_id,highest_degree,graduate_school,graduate_date,t_education_note) values(11,'博士','西北农林科技大学','2017-07-06 12:00:00','211和985');
insert into t_ustore_s_condition_0047_2(staff_id,highest_degree,graduate_school,graduate_date,t_education_note) values(12,'硕士','西北工业大学','2017-07-06 12:00:00','211和985');
insert into t_ustore_s_condition_0047_2(staff_id,highest_degree,graduate_school,graduate_date,t_education_note) values(15,'学士','西安建筑科技大学','2017-07-06 12:00:00','非211和985');
insert into t_ustore_s_condition_0047_2(staff_id,highest_degree,graduate_school,graduate_date,t_education_note) values(18,'硕士','西安理工大学','2017-07-06 12:00:00','非211和985');
insert into t_ustore_s_condition_0047_2(staff_id,highest_degree,graduate_school,graduate_date,t_education_note) values(20,'学士','北京师范大学','2017-07-06 12:00:00','211和985');

drop table if exists t_ustore_s_condition_0047_3;
create table t_ustore_s_condition_0047_3(col_1 integer,col_2 bigint constraint t_show_conditon_pk primary key,col_3 float8,col_4 decimal(12,6),col_5 bool,col_6 char(30),col_7 varchar2(50),col_8 varchar(30),col_9 interval day to second,col_10 timestamp,col_11 date,col_12 smalldatetime,col_13 timestamp without time zone,col_14 blob,col_15 clob,col_16 int[]) with (storage_type=ustore);

drop sequence if exists seq_s_condition_0047;
create sequence seq_s_condition_0047 increment by 1 start with 10;

insert into t_ustore_s_condition_0047_3 values(1,seq_s_condition_0047.nextval,   1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),  lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_s_condition_0047_3 values(1,seq_s_condition_0047.nextval, 1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','6','b'),rpad('abc','5','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),  lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_s_condition_0047_3 values(2,seq_s_condition_0047.nextval,   1+445.255,98*0.99, false,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','5','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_s_condition_0047_3 values(3,seq_s_condition_0047.nextval,   1+445.255,98*0.99, false,lpad('abc','4','@'),lpad('abc','4','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_s_condition_0047_3 values(3,seq_s_condition_0047.nextval,   1+445.255,98*0.99, false,lpad('abc','3','@'),lpad('abc','3','b'),rpad('abc','3','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'), to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'), lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_s_condition_0047_3 values(4,seq_s_condition_0047.nextval,   1+445.255,98*0.99,  true,lpad('abc','3','@'),lpad('abc','4','b'),rpad('abc','6','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),to_timestamp('12-sep-10 14:10:10.123000','dd-mon-yy hh24:mi:ss.ff'),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');

--step2:结合分析执行explain plan   expect:成功
explain plan for select /*+use_hash*/count(col_2) over (partition by col_1 order by col_2) as "count",col_8,col_9 from t_ustore_s_condition_0047_3 where col_5 != false group by col_1,col_2,col_8,col_9 order by 1,2,3;

--step3:查看系统表plan_table中关键字段信息   expect:成功
select operation,options,object_name,object_type,projection from plan_table order by 1;

--step4:清理plan_table   expect:成功
delete from plan_table;

--step5:结合分组执行explain plan   expect:成功
explain plan for select array_agg(name),sal from t_ustore_s_condition_0047_1 where b_boolean != true or id < 23 or id in (select staff_id from t_ustore_s_condition_0047_2 where highest_degree = '硕士')group by sal,id order by 1;

--step6:查看系统表plan_table中关键字段信息   expect:成功
select operation,options,object_name,object_type,projection from plan_table order by operation,projection;

--step7:清理环境   expect:成功
delete from plan_table;
drop table t_ustore_s_condition_0047_1 cascade;
drop table t_ustore_s_condition_0047_2 cascade;
drop table t_ustore_s_condition_0047_3 cascade;
drop sequence if exists seq_s_condition_0047;